package com.example.mycontact.DB

import android.annotation.SuppressLint
import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.graphics.Path.Op
import com.example.mycontact.bean.Contact

class ContactTable(var context: Context){
    //打开数据库
    fun OpenDB():SQLiteDatabase{
        val myDBHelper = MyDBHelper(context)
        val mydb = myDBHelper.writableDatabase
        return mydb
    }
    fun AddnewContact(newcontact:Contact):Boolean{
        var reslut = true
        val values = ContentValues().apply{
            put("name",newcontact.name)
            put("company",newcontact.company)
            put("position",newcontact.position)
            put("tel",newcontact.tel)
            put("mail",newcontact.mail)
            put("image",newcontact.image)
            put("isFavorite",newcontact.isFavorite)
        }
        if(OpenDB().insert("Contacts",null,values).toInt()==-1){
            reslut = false
        }
        return reslut
    }
    //查询 所有
    @SuppressLint("Range")
    fun findAllContact():ArrayList<Contact>{
        val contactList = ArrayList<Contact>()
        //从表执行查询，按关注状态排序
        val selectAll = "select * from Contacts order by isFavorite desc, name asc"
        val cursor = OpenDB().rawQuery(selectAll,null, null)
//        OpenDB().query("Contacts",null,null,null,null,null,null)
        //遍历查询的结果数据集，保存list中
        if(cursor.moveToFirst()){
            do{
                val contact = Contact()
                contact.id = cursor.getInt(cursor.getColumnIndex("id"))
                contact.name = cursor.getString(cursor.getColumnIndex("name"))
                contact.company = cursor.getString(cursor.getColumnIndex("company"))
                contact.position = cursor.getString(cursor.getColumnIndex("position"))
                contact.tel = cursor.getString(cursor.getColumnIndex("tel"))
                contact.mail = cursor.getString(cursor.getColumnIndex("mail"))
                contact.image = cursor.getBlob(cursor.getColumnIndex("image"))
                contact.isFavorite = cursor.getInt(cursor.getColumnIndex("isFavorite"))
                contactList.add(contact)
            }while(cursor.moveToNext())
        }else{
            //查询没有结果
            var contact = Contact()
            contact.name = "没有联系人"
            contactList.add(contact)
        }
        return contactList
    }
    //按条件查询
    @SuppressLint("Range")
    fun findContactByKey(key: String):ArrayList<Contact>{
        val contactList = ArrayList<Contact>()
        //从表执行 模糊查询 通配符 %:任意字符，按关注状态排序
        val selectAll = "select * from Contacts where name like '%$key%' or company like '%$key%' or position like '%$key%'"+
                " or tel like '%$key%' or mail like '%$key%' order by isFavorite desc, name asc"
        val cursor = OpenDB().rawQuery(selectAll,null, null)
//        OpenDB().query("Contacts",null,null,null,null,null,null)
        //遍历查询的结果数据集，保存list中
        if(cursor.moveToFirst()){
            do{
                val contact = Contact()
                contact.id = cursor.getInt(cursor.getColumnIndex("id"))
                contact.name = cursor.getString(cursor.getColumnIndex("name"))
                contact.company = cursor.getString(cursor.getColumnIndex("company"))
                contact.position = cursor.getString(cursor.getColumnIndex("position"))
                contact.tel = cursor.getString(cursor.getColumnIndex("tel"))
                contact.mail = cursor.getString(cursor.getColumnIndex("mail"))
                contact.image = cursor.getBlob(cursor.getColumnIndex("image"))
                contact.isFavorite = cursor.getInt(cursor.getColumnIndex("isFavorite"))
                contactList.add(contact)
            }while(cursor.moveToNext())
        }else{
            //查询没有结果
            var contact = Contact()
            contact.name = "无符合条件联系人"
            contactList.add(contact)
        }
        return contactList
    }
    
    // 按公司分组查询联系人
    @SuppressLint("Range")
    fun findContactByCompany():ArrayList<Contact>{
        val contactList = ArrayList<Contact>()
        val selectAll = "select * from Contacts order by company, name"
        val cursor = OpenDB().rawQuery(selectAll,null, null)
        if(cursor.moveToFirst()){
            do{
                val contact = Contact()
                contact.id = cursor.getInt(cursor.getColumnIndex("id"))
                contact.name = cursor.getString(cursor.getColumnIndex("name"))
                contact.company = cursor.getString(cursor.getColumnIndex("company"))
                contact.position = cursor.getString(cursor.getColumnIndex("position"))
                contact.tel = cursor.getString(cursor.getColumnIndex("tel"))
                contact.mail = cursor.getString(cursor.getColumnIndex("mail"))
                contact.image = cursor.getBlob(cursor.getColumnIndex("image"))
                contact.isFavorite = cursor.getInt(cursor.getColumnIndex("isFavorite"))
                contactList.add(contact)
            }while(cursor.moveToNext())
        }else{
            //查询没有结果
            var contact = Contact()
            contact.name = "没有联系人"
            contactList.add(contact)
        }
        return contactList
    }
    
    // 按职位分组查询联系人
    @SuppressLint("Range")
    fun findContactByPosition():ArrayList<Contact>{
        val contactList = ArrayList<Contact>()
        val selectAll = "select * from Contacts order by position, name"
        val cursor = OpenDB().rawQuery(selectAll,null, null)
        if(cursor.moveToFirst()){
            do{
                val contact = Contact()
                contact.id = cursor.getInt(cursor.getColumnIndex("id"))
                contact.name = cursor.getString(cursor.getColumnIndex("name"))
                contact.company = cursor.getString(cursor.getColumnIndex("company"))
                contact.position = cursor.getString(cursor.getColumnIndex("position"))
                contact.tel = cursor.getString(cursor.getColumnIndex("tel"))
                contact.mail = cursor.getString(cursor.getColumnIndex("mail"))
                contact.image = cursor.getBlob(cursor.getColumnIndex("image"))
                contact.isFavorite = cursor.getInt(cursor.getColumnIndex("isFavorite"))
                contactList.add(contact)
            }while(cursor.moveToNext())
        }else{
            //查询没有结果
            var contact = Contact()
            contact.name = "没有联系人"
            contactList.add(contact)
        }
        return contactList
    }
    fun deleteContactById(id:Int):Boolean{
        var result = false
        if(OpenDB().delete("Contacts","id=?", arrayOf(id.toString()))>0){
            result = true
        }
        return result
    }
    //修改
    fun updateContact(newcontact: Contact):Boolean{
        var result = false
        val values = ContentValues().apply{
            put("name",newcontact.name)
            put("company",newcontact.company)
            put("position",newcontact.position)
            put("tel",newcontact.tel)
            put("mail",newcontact.mail)
            put("image",newcontact.image)
            put("isFavorite",newcontact.isFavorite)
        }
        if(OpenDB().update("Contacts",values,"id=?", arrayOf(newcontact.id.toString()))>0){
            result = true
        }
        return result
    }
    
    // 设置联系人关注状态
    fun updateFavoriteStatus(contactId: Int, isFavorite: Int): Boolean {
        var result = false
        val values = ContentValues().apply {
            put("isFavorite", isFavorite)
        }
        if(OpenDB().update("Contacts", values, "id=?", arrayOf(contactId.toString())) > 0) {
            result = true
        }
        return result
    }
    
    // 切换联系人关注状态（关注/取消关注）
    fun toggleFavorite(contactId: Int): Boolean {
        var result = false
        val db = OpenDB()
        val selectQuery = "SELECT isFavorite FROM Contacts WHERE id = ?"
        val cursor = db.rawQuery(selectQuery, arrayOf(contactId.toString()))
        
        if(cursor.moveToFirst()) {
            val currentStatus = cursor.getInt(cursor.getColumnIndex("isFavorite"))
            val newStatus = if(currentStatus == 1) 0 else 1
            
            val values = ContentValues().apply {
                put("isFavorite", newStatus)
            }
            if(db.update("Contacts", values, "id=?", arrayOf(contactId.toString())) > 0) {
                result = true
            }
        }
        cursor.close()
        return result
    }
}